package com.archer.data.access.impl;

import com.archer.data.access.AppUserAccess;
import com.archer.data.model.AppUser;
import com.archer.data.model.PageObject;
import com.archer.datasource.JdbcTemplateManger;
import com.archer.utils.DesUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by YB on 2014/11/12.
 */
public class AppUserAccessImpl implements AppUserAccess {

    private final static String I_SQL = "insert into app_user (username, password, `group`) values (?, ?, ?)";

    private final static String Q_SQL = "select id, username, password, a.group from app_user as a";

    private final static String D_SQL = "delete from app_user where id=?";

    private final static String U_SQL = "update app_user a set username=?,password=?,a.group=? where id=?";

    private final static String C_SQL = "select count(id) from app_user a";

    @Override
    public AppUser insert(final AppUser appUser) {
        try {
            JdbcTemplate jdbcTemplate = JdbcTemplateManger.getJdbcTemplate();
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection con)
                        throws SQLException {
                    PreparedStatement ps = con.prepareStatement(I_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
                    ps.setString(1, appUser.getUsername());
                    ps.setString(2, DesUtil.encrypt(appUser.getPassword()));
                    ps.setString(3, appUser.getGroup());
                    return ps;
                }
            }, keyHolder);
            appUser.setId(keyHolder.getKey().intValue());
            return appUser;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public AppUser getByUserName(String username) {
        try {
            JdbcTemplate jdbcTemplate = JdbcTemplateManger.getJdbcTemplate();
            AppUser appUser = (AppUser) jdbcTemplate.queryForObject(Q_SQL + " where username = ?", new Object[]{username}, new BeanPropertyRowMapper(AppUser.class));
            appUser.setPassword(DesUtil.decrypt(appUser.getPassword()));
            return appUser;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public AppUser get(Integer id) {
        try {
            JdbcTemplate jdbcTemplate = JdbcTemplateManger.getJdbcTemplate();
            AppUser appUser = (AppUser) jdbcTemplate.queryForObject(Q_SQL + " where id = ?", new Object[]{id}, new BeanPropertyRowMapper(AppUser.class));
            appUser.setPassword(DesUtil.decrypt(appUser.getPassword()));
            return appUser;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    public List<AppUser> getAll() {
        try {
            JdbcTemplate jdbcTemplate = JdbcTemplateManger.getJdbcTemplate();
            List<AppUser> userInfoList = jdbcTemplate.query(Q_SQL, new BeanPropertyRowMapper(AppUser.class));
            for (AppUser au : userInfoList) {
                au.setPassword(DesUtil.decrypt(au.getPassword()));
            }
            return userInfoList;
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList<AppUser>();
        }
    }

    @Override
    public PageObject<AppUser> getListByNameAndGroup(String name, String group, PageObject<AppUser> pageObject) {
        try {
            JdbcTemplate jdbcTemplate = JdbcTemplateManger.getJdbcTemplate();
            StringBuilder where = new StringBuilder(" where 1=1");
            List<String> params = new ArrayList<String>();
            if (name != null && name.length() > 0) {
                where.append(" and username like ?");
                params.add("%" + name + "%");
            }
            if (group != null && group.length() > 0) {
                where.append(" and a.group like ?");
                params.add("%" + group + "%");
            }
            int count = jdbcTemplate.queryForInt(C_SQL + where.toString(), params.toArray());

            where.append(pageObject.getLimitStr());
            List<AppUser> userInfoList = jdbcTemplate.query(Q_SQL + where.toString(), params.toArray(), new BeanPropertyRowMapper(AppUser.class));
            pageObject.setTotal(count);
            for (AppUser au : userInfoList) {
                au.setPassword(DesUtil.decrypt(au.getPassword()));
            }
            pageObject.setList(userInfoList);
            return pageObject;
        } catch (Exception e) {
            e.printStackTrace();
            pageObject.setTotal(0);
            pageObject.setList(new ArrayList<AppUser>());
            return pageObject;
        }
    }

    @Override
    public int delete(Integer id) {
        try {
            JdbcTemplate jdbcTemplate = JdbcTemplateManger.getJdbcTemplate();
            int count = jdbcTemplate.update(D_SQL, new Object[]{id});
            return count;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int update(AppUser appUser) {
        try {
            JdbcTemplate jdbcTemplate = JdbcTemplateManger.getJdbcTemplate();
            int count = jdbcTemplate.update(U_SQL, new Object[]{appUser.getUsername(), DesUtil.encrypt(appUser.getPassword()), appUser.getGroup(), appUser.getId()});
            return count;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }
}
